<?php


class LoyerManager {
    public function __construct(Mypdo $db){
        $this->db= $db;
    }

    public function add(Loyer $loyer){
        $req = $this->db->prepare('INSERT INTO loyer ( date_loyer, id_loc, id_loca, solde_payer_loyer, remarque_loyer, statut_loyer ) VALUES
                  ( :date_loyer, :id_loc, :id_loca, :solde_payer_loyer, :remarque_loyer, :statut_loyer )');
        $req->bindValue(':date_loyer', $loyer->getDateLoyer(), PDO::PARAM_STR);
        $req->bindValue(':id_loc', $loyer->getIdLoc(), PDO::PARAM_INT);
        $req->bindValue(':id_loca', $loyer->getIdLoca(), PDO::PARAM_INT);
        $req->bindValue(':solde_payer_loyer', $loyer->getSoldePayerLoyer(), PDO::PARAM_STR);
        $req->bindValue(':remarque_loyer', $loyer->getRemarqueLoyer(), PDO::PARAM_STR);
        $req->bindValue(':statut_loyer', $loyer->getStatutLoyer(), PDO::PARAM_STR);


        $req->execute();

    }

    public function getList(){
        $listeLoyers = array();
        $sql = 'SELECT * FROM loyer ORDER BY date_loyer DESC';
        $req = $this->db->query($sql);
        while ($loyer = $req->fetch(PDO::FETCH_OBJ)){
            $listeLoyers[] = new Loyer($loyer);
        }
        $req->closeCursor();
        return $listeLoyers;

    }

    public function delLoyerByIdLoca($id_loca){

        $sql = 'DELETE FROM immoloc.loyer WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->execute();

    }
    public function delLoyerByIdLoc($id_loc){

        $sql = 'DELETE FROM immoloc.loyer WHERE id_loc = :id_loc';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_INT);
        $req->execute();

    }
    public function delLoyerByIdImmeu($id_immeu){

        $sql = 'DELETE FROM immoloc.loyer
         WHERE id_loc IN
         (SELECT id_loc FROM location WHERE id_immeu = :id_immeu )';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_immeu', $id_immeu, PDO::PARAM_INT);
        $req->execute();

    }
    public function delLoyerByIdPropri($id_propri){

        $sql = 'DELETE FROM immoloc.loyer WHERE id_loc IN
        (SELECT id_loc FROM location l
        INNER JOIN immeuble i ON i.id_immeu = l.id_immeu
        WHERE id_propri = :id_propri)';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_propri', $id_propri, PDO::PARAM_INT);
        $req->execute();

    }

    public function recupMois(){

            $mois = array('1'=>'Janvier','2'=>'Fevrier','3'=>'Mars','4'=>'Avril','5'=>'Mai','6'=>'Juin','7'=>'Juillet','8'=>'Aout','9'=>'Septembre','10'=>'Octobre','11'=>'Novembre','12'=>'Decembre');
        $listeDate = array();
        $sql = "SELECT distinct month(date_loyer) as mois, year(date_loyer) as annee FROM loyer ORDER BY date_loyer ";
        $req = $this->db->query($sql);
        foreach ($req->fetchAll(PDO::FETCH_ASSOC) as $value) {
            $zero = $value['mois'] < 10?'0':'';
            $listeDate[$value['annee'].'-'.$zero.$value['mois']] = $mois[$value['mois']].' '.$value['annee'];
        }

        return $listeDate;
    }

    public function etatLoyerByLocaLocDate($id_loca, $id_loc, $date)
    {
        $sql = 'SELECT statut_loyer  FROM loyer WHERE id_loca = :id_loca AND id_loc = :id_loc AND date_loyer LIKE :dates';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_INT);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);
        $req->execute();
        $res = $req->fetch(PDO::FETCH_ASSOC);
        if($res == false){
            throw new ImmolocException('Pas d\'appel');
        }
        return $res['statut_loyer'];
    }

    public function getLoyerByMoisLocLoca($date, $id_loc, $id_loca)
    {
        $sql = 'SELECT *  FROM loyer WHERE id_loca = :id_loca AND id_loc = :id_loc AND date_loyer LIKE :dates';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->bindValue(':id_loc', $id_loc, PDO::PARAM_INT);
        $req->bindValue(':dates', $date.'-%', PDO::PARAM_STR);
        $req->execute();
        return new Loyer($req->fetch(PDO::FETCH_ASSOC));
    }

    public function update(Loyer $loyer){
        //var_dump($loyer);
        $sql = 'UPDATE loyer SET  solde_payer_loyer = :solde_payer_loyer, remarque_loyer = :remarque_loyer, statut_loyer = :statut_loyer
                WHERE id_loca = :id_loca AND id_loc = :id_loc AND date_loyer LIKE :date_loyer';
        $req = $this->db->prepare($sql);
        $req->bindValue(':date_loyer', $loyer->getDateLoyer(), PDO::PARAM_STR);
        $req->bindValue(':id_loc', $loyer->getIdLoc(), PDO::PARAM_INT);
        $req->bindValue(':id_loca', $loyer->getIdLoca(), PDO::PARAM_INT);
        $req->bindValue(':solde_payer_loyer', $loyer->getSoldePayerLoyer(), PDO::PARAM_STR);
        $req->bindValue(':remarque_loyer', $loyer->getRemarqueLoyer(), PDO::PARAM_STR);
        $req->bindValue(':statut_loyer', $loyer->getStatutLoyer(), PDO::PARAM_STR);
        $req->execute();
    }

    public function getListByIdLoca($id_loca)
    {
        $sql = 'SELECT *  FROM loyer WHERE id_loca = :id_loca';
        $req = $this->db->prepare($sql);
        $req->bindValue(':id_loca', $id_loca, PDO::PARAM_INT);
        $req->execute();

        $liste = array();
        foreach ($req->fetchAll(PDO::FETCH_ASSOC) as $value) {
            $liste[] = new Loyer($value);
        }

        return $liste;
    }

    public function getListByStatut($statut_loyer)
    {
        $sql = 'SELECT *  FROM loyer WHERE statut_loyer = :statut_loyer ORDER BY date_loyer';
        $req = $this->db->prepare($sql);
        $req->bindValue(':statut_loyer', $statut_loyer, PDO::PARAM_INT);
        $req->execute();

        $liste = array();
        foreach ($req->fetchAll(PDO::FETCH_ASSOC) as $value) {
            $liste[] = new Loyer($value);
        }

        return $liste;
    }

}


